package model.db

import java.sql.*

/**
 * sqlite帮助类，直接创建该类示例，并调用相应的借口即可对sqlite数据库进行操作
 *
 * 本类基于 sqlite jdbc v56
 *
 * @author
 */
class SqliteHelper(private val dbFilePath: String) {
    private var connection: Connection?
    private var statement: Statement? = null
    private var resultSet: ResultSet? = null

    /**
     * 构造函数
     * @param dbFilePath sqlite db 文件路径
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    init {
        connection = getConnection(dbFilePath)
    }


    /**
     * 获取数据库连接
     * @param dbFilePath db文件路径
     * @return 数据库连接
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    @Throws(ClassNotFoundException::class, SQLException::class)
    fun getConnection(dbFilePath: String): Connection? {
        var conn: Connection? = null
        // 1、加载驱动
        Class.forName("org.sqlite.JDBC")
        // 2、建立连接
        // 注意：此处有巨坑，如果后面的 dbFilePath 路径太深或者名称太长，则建立连接会失败
        conn = DriverManager.getConnection("jdbc:sqlite:$dbFilePath")
        return conn
    }

    /**
     * 执行sql查询
     * @param sql sql select 语句
     * @param rse 结果集处理类对象
     * @return 查询结果
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    @Throws(SQLException::class, ClassNotFoundException::class)
    fun <T> executeQuery(sql: String?, rse: ResultSetExtractor<T>): T {
        return try {
            resultSet = getStatement()!!.executeQuery(sql)
            rse.extractData(resultSet)
        } finally {
            destroyed()
        }
    }

    /**
     * 执行select查询，返回结果列表
     *
     * @param sql sql select 语句
     * @param rm 结果集的行数据处理类对象
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    @Throws(SQLException::class, ClassNotFoundException::class)
    fun <T> executeQuery(sql: String?, rm: RowMapper<T>): List<T> {
        val rsList: MutableList<T> = ArrayList()
        try {
            resultSet = getStatement()!!.executeQuery(sql)
            while (resultSet!!.next()) {
                rsList.add(rm.mapRow(resultSet, resultSet!!.row))
            }
        } finally {
            destroyed()
        }
        return rsList
    }

    /**
     * 执行数据库更新sql语句
     * @param sql
     * @return 更新行数
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    @Throws(SQLException::class, ClassNotFoundException::class)
    fun executeUpdate(sql: String?): Int {
        return try {
            getStatement()!!.executeUpdate(sql)
        } finally {
            destroyed()
        }
    }

    /**
     * 执行多个sql更新语句
     * @param sqls
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    @Throws(SQLException::class, ClassNotFoundException::class)
    fun executeUpdate(vararg sqls: String?) {
        try {
            for (sql in sqls) {
                getStatement()!!.executeUpdate(sql)
            }
        } finally {
            destroyed()
        }
    }

    /**
     * 执行数据库更新 sql List
     * @param sqls sql列表
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    @Throws(SQLException::class, ClassNotFoundException::class)
    fun executeUpdate(sqls: List<String?>) {
        try {
            for (sql in sqls) {
                getStatement()!!.executeUpdate(sql)
            }
        } finally {
            destroyed()
        }
    }

    @Throws(ClassNotFoundException::class, SQLException::class)
    private fun getConnection(): Connection? {
        if (null == connection) connection = getConnection(dbFilePath)
        return connection
    }

    @Throws(SQLException::class, ClassNotFoundException::class)
    private fun getStatement(): Statement? {
        if (null == statement) statement = getConnection()!!.createStatement()
        return statement
    }

    /**
     * 数据库资源关闭和释放
     */
    fun destroyed() {
        try {
            if (null != connection) {
                connection!!.close()
                connection = null
            }
            if (null != statement) {
                statement!!.close()
                statement = null
            }
            if (null != resultSet) {
                resultSet!!.close()
                resultSet = null
            }
        } catch (e: SQLException) {
            println("Sqlite数据库关闭时异常 $e")
        }
    }

    /**
     * 是否自动提交事务
     */
    @Throws(SQLException::class)
    fun setAutoCommit(status: Boolean?) {
        connection!!.autoCommit = status!!
    }
}